package com.njust.diary.dao;

import cn.hutool.core.util.StrUtil;
import com.njust.diary.po.Comment;
import com.njust.diary.po.Like;
import com.njust.diary.po.Note;
import com.njust.diary.po.NoteType;
import com.njust.diary.vo.NoteVo;

import java.util.ArrayList;
import java.util.List;

public class NoteDao {

    public int addOrUpdate(Note note) {

     String sql ="";

     List<Object> params =new ArrayList<>();
     params.add(note.getTypeId());
     params.add(note.getTitle());
     params.add(note.getContent());

     if(note.getNoteId()==null){//新建
         sql="insert into tb_note (typeId,title,content,pubTime,lon,lat,uname) values (?,?,?,now(),?,?,?)";
         params.add(note.getLon());
         params.add(note.getLat());
         params.add(note.getUname());
     }
      else{//更新
          sql="update tb_note set typeId = ?, title = ?, content = ? where noteId = ?";
          params.add(note.getNoteId());
     }
     int row =BaseDao.executeUpdate(sql,params);
     return row;


    }

    public long findNoteCount(Integer userId,String title,String date,String type) {
        String sql ="SELECT count(1) FROM tb_note n INNER JOIN tb_note_type t on n.typeId =t.typeId WHERE userId = ?";

        List<Object> params=new ArrayList<>();
        params.add(userId);
        if(!StrUtil.isBlank(title)){
            //标题查询
             sql += " and title like concat('%',?,'%') ";
             params.add(title);
        }else if(!StrUtil.isBlank(date)){
             sql += " and date_format(pubTime,'%Y年%m月') = ?";
             params.add(date);
        }else if(!StrUtil.isBlank(type)){
             sql += " and n.typeId = ?";
             params.add(type);
        }

        long count = (long) BaseDao.findSingleValue(sql,params);
        return  count;

    }

    public List<Note> findNoteListByPage(Integer userId, Integer index, Integer pageSize,String title,String date,String type) {
      String sql ="SELECT noteId,title,pubTime,typeName,n.uname FROM tb_note n INNER JOIN tb_note_type t on n.typeId =t.typeId";


        List<Object> params=new ArrayList<>();

        if(!StrUtil.isBlank(title)){
            //标题查询
            sql+= " where title like concat('%',?,'%') ";
            params.add(title);
        }else if(!StrUtil.isBlank(date)){
            sql += " and date_format(pubTime,'%Y年%m月') = ?";
            params.add(date);
        }else if(!StrUtil.isBlank(type)){
            sql += " and n.typeId = ?";
            params.add(type);
        }
        sql +=" order by pubTime desc limit ?,?";


        params.add(index);
        params.add(pageSize);

        List<Note> noteList=BaseDao.queryRows(sql,params,Note.class);
        return noteList;
    }

    public List<NoteVo> findNoteCountByDate(Integer userId) {
        String sql = "SELECT count(1) noteCount,DATE_FORMAT(pubTime,'%Y年%m月') groupName FROM tb_note n " +
                " INNER JOIN tb_note_type t ON n.typeId = t.typeId WHERE userId = ? " +
                " GROUP BY DATE_FORMAT(pubTime,'%Y年%m月')" +
                " ORDER BY DATE_FORMAT(pubTime,'%Y年%m月') DESC ";
        List<Object> params=new ArrayList<>();
        params.add(userId);
        List<NoteVo> list = BaseDao.queryRows(sql,params,NoteVo.class);
        return  list;


    }

    public List<NoteVo> findNoteCountByType(Integer userId) {
        // 定义SQL语句
        String sql = "SELECT count(noteId) noteCount, t.typeId, typeName groupName FROM tb_note n " +
                " RIGHT JOIN tb_note_type t ON n.typeId = t.typeId WHERE userId = ? " +
                " GROUP BY t.typeId ORDER BY COUNT(noteId) DESC ";

        // 设置参数
        List<Object> params = new ArrayList<>();
        params.add(userId);

        // 调用BaseDao的查询方法
        List<NoteVo> list = BaseDao.queryRows(sql, params, NoteVo.class);

        return list;
    }

    public Note findNoteById(String noteId) {
        String sql ="select noteId,title,content,pubTime,typeName,n.typeId,n.uname from tb_note n inner join tb_note_type t on n.typeId=t.typeId where noteId = ?";
        List<Object> params = new ArrayList<>();
        params.add(noteId);
        Note note = (Note) BaseDao.queryRow(sql,params,Note.class);
        return note;


    }

    public int deleteNoteById(String noteId) {
        String sql="delete from tb_note where noteId = ?";
        List<Object> params = new ArrayList<>();
        params.add(noteId);
        int row =BaseDao.executeUpdate(sql,params);
        return row;
    }

    public List<Note> queryNoteList(Integer userId) {
        String sql = "select lon,lat from tb_note n inner join tb_note_type t on n.typeId = t.typeId where userId = ?";
        List<Object> params = new ArrayList<>();
        params.add(userId);
        List<Note> list = BaseDao.queryRows(sql,params,Note.class);
        return  list;
    }

    public List<Comment> findCommentListByNoteId(String noteId) {
        String sql ="select uname,content from tb_comment where noteId = ?";
        List<Object> params=new ArrayList<>();
        params.add(noteId);
        List<Comment> list = BaseDao.queryRows(sql,params,Comment.class);
        return list;
    }

    public int addComment(Comment comment) {
        String sql="insert into tb_comment (noteId,content,uname) values (?,?,?)";
        List<Object> params =new ArrayList<>();
        params.add(comment.getNoteId());
        params.add(comment.getContent());
        params.add(comment.getUname());
        int row =BaseDao.executeUpdate(sql,params);
        return row;
    }

    public Like findLikebyUserId(Integer userId) {
        String sql ="select userId,noteId from tb_like where userId = ?";
        List<Object> params = new ArrayList<>();
        params.add(userId);
        Like like = (Like) BaseDao.queryRow(sql,params,Like.class);
        return like;

    }

    public Long findlikescountByNoteId(String noteId) {
        String sql ="select count(1) from tb_like where noteId = ?";
        List<Object> params = new ArrayList<>();
        params.add(noteId);
        Long count = (long) BaseDao.findSingleValue(sql,params);
        return count;

    }

    public void deleteLikeById(Integer noteId2, Integer userId) {
        String sql="delete from tb_like where userId = ? and noteId = ?";
        List<Object> params = new ArrayList<>();
        params.add(userId);
        params.add(noteId2);
        int row =BaseDao.executeUpdate(sql,params);


    }

    public void insertLikeById(Integer noteId2, Integer userId) {
        String sql="insert into tb_like (userId,noteId) values (?,?)";
        List<Object> params = new ArrayList<>();
        params.add(userId);
        params.add(noteId2);
        int row =BaseDao.executeUpdate(sql,params);

    }
}
